#! /usr/bin/env python   
# -*- coding: utf-8 -*- 
# 
import MySQLdb

class Model:

    def __init__(self, config):
        try:
            # 打开数据库连接
            self.db = MySQLdb.connect(host=config['host'], user=config['user'], passwd=config['password'], \
                db=config['database'], charset=config['charset'])
            
            # 使用cursor()方法获取操作游标 
            self.cursor = self.db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            self.self = None
        except Exception as e:
            print config
            raise e

    def add(self, table, data):
        fields = []
        formats = []
        params = []
        for key,value in data.items():
            fields.append(key)
            formats.append('%s')
            params.append(value)

        sql = "INSERT INTO %s(%s) VALUES (%s)" % (table, ",".join(fields), ','.join(formats))
        #print sql, params
        self.execute(sql, tuple(params))
        new_id = self.cursor.lastrowid

        return new_id

    def incr(self, table, where, fields):
        sql_where = []
        sql_data = []
        params = []
        for k in fields:
            sql_data.append("%s=%s+1" % (k, k))

        for k,v in where.items():
            sql_where.append("%s = %%s" % (k))
            params.append(v)

        sql = "UPDATE %s SET %s WHERE %s" % (table, ','.join(sql_data), ' AND '.join(sql_where))
        #print sql,params
        self.execute(sql, tuple(params))

        return self.db.affected_rows()

    def desc(self, table, where, fields):
        sql_where = []
        sql_data = []
        params = []
        for k in fields:
            sql_data.append("%s=%s-1" % (k, k))

        for k,v in where.items():
            sql_where.append("%s = %%s" % (k))
            params.append(v)

        sql = "UPDATE %s SET %s WHERE %s" % (table, ','.join(sql_data), ' AND '.join(sql_where))
        #print sql,params
        self.execute(sql, tuple(params))

        return self.db.affected_rows()

    def save(self, table, where, data):
        sql_where = []
        sql_data = []
        params = []
        for k,v in data.items():
            sql_data.append("%s = %%s" % (k))
            params.append(v)

        for k,v in where.items():
            sql_where.append("%s = %%s" % (k))
            params.append(v)

        sql = "UPDATE %s SET %s WHERE %s" % (table, ','.join(sql_data), ' AND '.join(sql_where))
        #print sql,params
        self.execute(sql, tuple(params))

        return self.db.affected_rows()

    def count(self, table, data):
        where = []
        params = []
        for key,value in data.items():
            if type(value)==tuple:
                where.append("%s %s %%s" % (key, value[0]))
                params.append(value[1])
            else:
                where.append("%s = %%s" % (key))
                params.append(value)

        sql = "SELECT count(*) AS total FROM %s WHERE %s" % (table, ' AND '.join(where))

        self.execute(sql, tuple(params))
        ret = self.cursor.fetchone()
        return ret['total']

    def find(self, table, data, order='id ASC'):
        where = []
        params = []
        for key,value in data.items():
            if type(value)==tuple:
                where.append("%s %s %%s" % (key, value[0]))
                params.append(value[1])
            else:
                where.append("%s = %%s" % (key))
                params.append(value)

        sql = "SELECT * FROM %s WHERE %s ORDER BY %s LIMIT 1" % (table, ' AND '.join(where), order)

        self.execute(sql, tuple(params))
        return self.cursor.fetchone()

    def delete(self, table, data):
        where = []
        params = []
        for key,value in data.items():
            if type(value)==tuple:
                where.append("%s %s %%s" % (key, value[0]))
                params.append(value[1])
            else:
                where.append("%s = %%s" % (key))
                params.append(value)

        sql = "DELETE FROM %s WHERE %s" % (table, ' AND '.join(where))
        print sql, params
        self.execute(sql, tuple(params))
        return self.db.affected_rows()

    def select(self, table, data={}, order='id ASC'):
        where = []
        params = []
        for key,value in data.items():
            where.append("%s = %%s" % (key))
            params.append(value)
        else:
            where.append("1")

        sql = "SELECT * FROM %s WHERE %s ORDER BY %s" % (table, ' AND '.join(where), order)
        #print sql, params
        self.execute(sql, tuple(params))
        return self.cursor.fetchall()
   
    def query(self, sql, params):
        self.execute(sql, params)
        return self.cursor.fetchall()

    def execute(self, sql, params):
        self.error = None
        try:
            # print sql, params
            # 执行sql语句
            self.cursor.execute(sql, params)
            # 提交到数据库执行
            self.db.commit()
 
        except MySQLdb.Error, e:
            self.error = str(e)
            print "Execute Error:%s" % str(e)

        except Exception, e:
            self.error = str(e)
            print "Execute Error:%s" % str(e)
            # 发生错误时回滚
            self.db.rollback()

    def getError(self):
        return self.error

    def close(self):
        if self.db:
            self.db.close()
            self.db = None

    def __del__(self):
        # print 'close'
        self.close()


